# coding:utf-8
import xlrd
import MySQLdb
import urllib2
import json
import sys
import traceback
import logging
import os
import time

'''
导入加油站终端信息
'''

reload(sys)
sys.setdefaultencoding('utf-8')

timeFt = '%Y-%m-%d %H:%M:%S'

# 获取桌面路径
desktop = os.path.join(os.path.expanduser("~"), 'Desktop')

logger = logging.getLogger()


def initlog():
    '''
    日志配置
    '''
    global logger
    logging.basicConfig(
        filename=desktop + '/gasstation.log',
        filemode='a',  # r只读，w可写，a追加
        level=logging.DEBUG,
        format='%(asctime)s  %(filename)s : %(levelname)s  %(message)s',
        datefmt='%Y-%m-%d %A %H:%M:%S'
    )
    logger.setLevel(logging.DEBUG)
    formatter = logging.Formatter('%(asctime)s  %(filename)s : %(levelname)s  %(message)s')
    ch = logging.StreamHandler()
    ch.setLevel(logging.DEBUG)
    ch.setFormatter(formatter)
    logger.addHandler(ch)


# 自定义异常
class OilException(Exception):
    pass


dict = {}


def loadCoord():
    '''
    加载坐标表
    :return:
    '''
    xls = xlrd.open_workbook('C:\\Users\\seer\\Desktop\\b.xlsx')

    sheet = xls.sheet_by_name(u'Sheet1')

    nrows = sheet.nrows
    for rownum in range(2, nrows):
        # ('终端号','经度','纬度','联系电话','联系人')
        merchId = sheet.cell(rownum, 8).value.encode('utf-8')
        coord = sheet.cell(rownum, 5).value.encode('utf-8')
        if coord == '':
            continue
        coord = coord.split(',')
        lng = coord[0]
        lat = coord[1]
        data = (
            merchId, lng, lat, sheet.cell(rownum, 6).value.encode('utf-8'), sheet.cell(rownum, 7).value.encode('utf-8'))
        dict[merchId] = data
    print '加载坐标表完成 %d ' % dict.__len__()
    return data


def qryDetail(terminalId):
    '''
    查询详细信息
    :param merchId:
    :return:
    '''
    data = dict.get(terminalId)
    if not data:
        raise OilException(u'没有这个加油站的信息')
    lng = data[1]
    lat = data[2]
    phone = data[3]
    contacts = data[4]
    return lng, lat, phone, contacts


def trans2bd(lng, lat):
    '''
    转换百度坐标系
    :param lng: 经度
    :param lat: 纬度
    :return: 百度经度，百度纬度
    '''
    url = 'http://api.map.baidu.com/geoconv/v1/?from=1&to=5&ak=xksmBxs7EH1Gq0R7tqzTtiPHwUIFr4we&coords={lng},{lat}'.format(
        lng=lng,
        lat=lat
    )
    respons = urllib2.urlopen(url=url)
    jsondict = json.loads(respons.read())
    if jsondict['status'] != 0:
        raise OilException('百度坐标系转换失败')
    return jsondict['result'][0]['x'], jsondict['result'][0]['y']


# 有默认值的参数要放在没有默认值的参数的后面
def saveGasStation(connection,
                   terminalId=None,
                   merchId=None,
                   merchName=None,
                   province=None,
                   provinceCode=None,
                   city=None,
                   cityCode=None,
                   address=None,
                   lng=None,
                   lat=None,
                   lngBd=None,
                   latBd=None,
                   oils=None,
                   phone=None,
                   contacts=None,
                   onWay=None,
                   gasolineBrand=None,
                   status=None):
    '''
    保存到数据库
    :return:
    '''
    sql = '''INSERT INTO olicard.es_gs (terminalId, merchId, merchName, province, provinceCode, city, cityCode, address, lng, lat, lngBd, latBd, oils, phone, contacts, onWay,gasolineBrand,status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s)'''

    cursor = connection.cursor()
    try:
        # 不要用字符串格式化sql参数，会出问题的
        cursor.execute(sql, (
            terminalId, merchId, merchName, province, provinceCode, city, cityCode, address, lng, lat, lngBd, latBd,
            oils,
            phone, contacts, onWay, gasolineBrand, status))
        connection.commit()
        print '保存 %s 完成' % terminalId
    except Exception, e:
        connection.rollback()
        raise


def qryProvinceCode(name, connection):
    '''
    查询省的编码
    :param name:
    :param connection:
    :return: code
    '''
    sql = 'select provinceCode from sys_province where provinceName like \'%%%s%%\'' % name
    cursor = connection.cursor()
    cursor.execute(query=sql)
    code = cursor.fetchone()
    if code:
        return code[0].encode('utf-8')
    raise OilException('省编码查询为空')


def qryCityCode(name, connection):
    '''
    查询市的编码
    :param name:
    :param connection:
    :return: code
    '''
    sql = 'select cityCode from sys_city where cityName like \'%%%s%%\'' % name
    cursor = connection.cursor()
    cursor.execute(query=sql)
    code = cursor.fetchone()
    if code:
        return code[0].encode('utf-8')
    raise OilException('市编码查询为空')


def qryOilLine(param):
    '''
    查询加油站品牌
    :param param:
    :return:
    '''

    # 中石化    0
    # 中石油    1
    # 壳牌    3
    # 其他    4
    # 中海油    2

    if '中石化' in param:
        return 0
    if '中石油' in param:
        return 1
    if '壳牌' in param:
        return 3
    if '中海油' in param:
        return 2

    return 4


def main():
    '''
    主函数
    :return:
    '''
    initlog()

    # 加载坐标信息
    loadCoord()

    # 打开数据库连接
    connection = None
    try:
        connection = MySQLdb.connect(host='10.180.29.19',
                                     user='mobapp',
                                     passwd='mobapp123',
                                     db='olicard',
                                     port=3306,
                                     charset='utf8')

        xls = xlrd.open_workbook('C:\\Users\\seer\\Desktop\\a.xlsx')

        sheet = xls.sheet_by_name(u'Sheet1')

        nrows = sheet.nrows

        for rownum in range(1, nrows):
            try:
                terminalId = sheet.cell(rownum, 4).value.encode('utf-8')
                merchId = sheet.cell(rownum, 2).value.encode('utf-8')
                merchName = sheet.cell(rownum, 3).value.encode('utf-8')
                province = sheet.cell(rownum, 0).value.encode('utf-8')
                provinceCode = qryProvinceCode(province, connection)
                city = sheet.cell(rownum, 1).value.encode('utf-8')
                cityCode = qryCityCode(city, connection)
                address = sheet.cell(rownum, 7).value.encode('utf-8')
                lng, lat, phone, contacts = qryDetail(terminalId)

                lngBd, latBd = trans2bd(lng, lat)

                # 是否在高速路上 0-否，1-是
                onWay = sheet.cell(rownum, 8).value.encode('utf-8')
                if onWay == '否':
                    onWay = 0
                else:
                    onWay = 1

                # 加油站状态：0-正常，1-暂停
                status = sheet.cell(rownum, 5).value.encode('utf-8').strip()
                if status is None or status == '':
                    status = 0
                else:
                    status = 1

                # 油品牌
                gasolineBrand = qryOilLine(merchName)

                saveGasStation(connection=connection,
                               terminalId=terminalId,
                               merchId=merchId,
                               merchName=merchName,
                               province=province,
                               provinceCode=provinceCode,
                               city=city,
                               cityCode=cityCode,
                               address=address,
                               lng=lng,
                               lat=lat,
                               lngBd=lngBd,
                               latBd=latBd,
                               phone=phone,
                               contacts=contacts,
                               onWay=onWay,
                               gasolineBrand=gasolineBrand,
                               status=status
                               )

                if rownum % 200 == 0:
                    print '%s 休眠20s' % rownum
                    time.sleep(20)

            except OilException, ex:
                print '终端号：%s，失败原因：%s' % (terminalId, ex)
                # logger.info('终端号：%s，商户名：%s， 地址：%s', terminalId, merchName, address)
                continue
    except Exception, e:
        print traceback.format_exc()
    finally:
        if connection:
            connection.close()


def test():
    trans2bd(116.020829,
             36.437016)


if __name__ == '__main__':
    main()
